﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;


namespace dkGameTool
{
    public class DataBaseManager
    {
        private static readonly DataBaseManager _instance = new DataBaseManager();

        public static DataBaseManager GetInstance { get => _instance; }

        private SqliteConnection connection;

        private DataTable FilterTable { get; set; } = null;

        public event EventHandler RecordsChanged;//当前产量信息发生变化

        public DataBaseManager() {

            InitDataBase();
        }

        ~DataBaseManager()
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                connection.Close();
            }
        }


        public DataTable getDataSkill(string startstamp)
        {

            if (connection.State == ConnectionState.Open)
            {
                string filterstr = "SELECT name, code, path, combo FROM skill";
                SqliteCommand cmd = new SqliteCommand(filterstr, connection);
                SqliteDataReader sdr = cmd.ExecuteReader();

                DataTable data = new DataTable();
                data.Load(sdr, LoadOption.OverwriteChanges);
                FilterTable = data;
                return data;
            }
            else
            {
                FilterTable = null;
                return null;
            }
        }

        public DataTable getDataChallenge(string startstamp)
        {

            if (connection.State == ConnectionState.Open)
            {
                string filterstr = "SELECT * FROM challenge";
  
                SqliteCommand cmd = new SqliteCommand(filterstr, connection);
                SqliteDataReader sdr = cmd.ExecuteReader();

                DataTable data = new DataTable();
                data.Load(sdr, LoadOption.OverwriteChanges);
                FilterTable = data;
                return data;
            }
            else
            {
                FilterTable = null;
                return null;
            }
        }


        public DataTable getDataChooseChallenge(string deviceId)
        {

            if (connection.State == ConnectionState.Open)
            {
                string filterstr = string.Format("SELECT a.code as code,  b.name as name, a.deviceId as deviceId FROM choose_challenge a left join challenge b on a.code = b.code where a.deviceId = '{0}'", deviceId);

                SqliteCommand cmd = new SqliteCommand(filterstr, connection);
                SqliteDataReader sdr = cmd.ExecuteReader();

                DataTable data = new DataTable();
                data.Load(sdr, LoadOption.OverwriteChanges);
                FilterTable = data;
                return data;
            }
            else
            {
                FilterTable = null;
                return null;
            }
        }

        public Boolean getCodeIsExist(string code)
        {
            if (connection.State == ConnectionState.Open)
            {
                string filterstr = string.Format("select code from img_res where code = '{0}'", code);

                SqliteCommand cmd = new SqliteCommand(filterstr, connection);
                SqliteDataReader sdr = cmd.ExecuteReader();

                DataTable data = new DataTable();
                data.Load(sdr, LoadOption.OverwriteChanges);
             
                return data.Rows.Count > 0;
            }
            else
            {
                return false;
            }
        }

        public void ExcuterSql(string sql)
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                var addcmd = new SqliteCommand();
                addcmd.Connection = connection;
                addcmd.CommandText = sql;
                addcmd.ExecuteReader();
                RecordsChanged?.Invoke(null, EventArgs.Empty);
            }
        }


        public DataTable getDataChooseSkill(string deviceId)
        {

            if (connection.State == ConnectionState.Open)
            {
                
                string filterstr = string.Format("SELECT a.deviceId, b.code, b.name, a.weight FROM choose_skill a left join skill b on a.skill_code = b.code where a.deviceId = '{0}'", deviceId);

                SqliteCommand cmd = new SqliteCommand(filterstr, connection);
                SqliteDataReader sdr = cmd.ExecuteReader();

                DataTable data = new DataTable();
                data.Load(sdr, LoadOption.OverwriteChanges);
                FilterTable = data;
                return data;
            }
            else
            {
                FilterTable = null;
                return null;
            }
        }


        public void AddChooseSkillRecord(string deivceId, string code, int weight)
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                var addcmd = new SqliteCommand();
                addcmd.Connection = connection;
                addcmd.CommandText = "INSERT INTO choose_skill (deviceId, skill_code, weight) VALUES (@deivceId, @code, @weight)";
                addcmd.Parameters.AddWithValue("@deivceId", deivceId);
                addcmd.Parameters.AddWithValue("@code", code);
                addcmd.Parameters.AddWithValue("@weight", weight);
                addcmd.ExecuteReader();
                RecordsChanged?.Invoke(null, EventArgs.Empty);
            }
        }

        public void DelSkillAllRecord(string deivceId)
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                var addcmd = new SqliteCommand();
                addcmd.Connection = connection;
                addcmd.CommandText = string.Format("Delete from choose_skill where deviceId = '{0}'", deivceId);
                addcmd.ExecuteReader();
                RecordsChanged?.Invoke(null, EventArgs.Empty);
            }
        }

        public void AddChooseChallengeRecord(string deivceId, string code, string nd)
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                var addcmd = new SqliteCommand();
                addcmd.Connection = connection;
                addcmd.CommandText = "INSERT INTO choose_challenge (deviceId, code, nd) VALUES (@deivceId, @code, @nd)";
                addcmd.Parameters.AddWithValue("@deivceId", deivceId);
                addcmd.Parameters.AddWithValue("@code", code);
                addcmd.Parameters.AddWithValue("@nd", nd);
                addcmd.ExecuteReader();
                RecordsChanged?.Invoke(null, EventArgs.Empty);
            }
        }

        public void DelChallengeAllRecord(string deivceId)
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                var addcmd = new SqliteCommand();
                addcmd.Connection = connection;
                addcmd.CommandText = string.Format("Delete from choose_challenge where deviceId = '{0}'", deivceId);
                addcmd.ExecuteReader();
                RecordsChanged?.Invoke(null, EventArgs.Empty);
            }
        }


        public DataTable getGameStatus(string deviceId)
        {

            if (connection.State == ConnectionState.Open)
            {
                string filterstr = string.Format("select deviceId,status,programe,heartbeat from game_status where deviceId = '{0}'", deviceId); ;
                SqliteCommand cmd = new SqliteCommand(filterstr, connection);
                SqliteDataReader sdr = cmd.ExecuteReader();

                

                DataTable data = new DataTable();
                data.Load(sdr, LoadOption.OverwriteChanges);
                FilterTable = data;
                return data;
            }
            else
            {
                FilterTable = null;
                return null;
            }
        }


        public void AddGameStatusRecord(string deviceId, int status, string programe)
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                var addcmd = new SqliteCommand();
                addcmd.Connection = connection;
                addcmd.CommandText = "INSERT INTO game_status (deviceId, status, programe) VALUES (@deviceId, @status, @programe)";
                addcmd.Parameters.AddWithValue("@deviceId", deviceId);
                addcmd.Parameters.AddWithValue("@status", status);
                addcmd.Parameters.AddWithValue("@programe", programe);
                addcmd.ExecuteReader();
                RecordsChanged?.Invoke(null, EventArgs.Empty);
            }
        }

        public void UpdateGameStatusRecord(string deviceId, int status, string programe )
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                var addcmd = new SqliteCommand();
                addcmd.Connection = connection;
                string sql = string.Format("update game_status set status = {1}, programe = '{2}' where deviceId = '{0}'", deviceId, status, programe);
                addcmd.CommandText = sql;
                addcmd.ExecuteReader();
                RecordsChanged?.Invoke(null, EventArgs.Empty);
            }
        }


        public void UpdateGameGkRecord(string deviceId, bool zxtz, bool tbxd, bool rctz)
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                var addcmd = new SqliteCommand();
                addcmd.Connection = connection;
                string sql = string.Format("update game_status set zxtz = {1}, tbxd = {2}, rctz = {3} where deviceId = '{0}'", deviceId, zxtz, tbxd, rctz);
                addcmd.CommandText = sql;
                addcmd.ExecuteReader();
                RecordsChanged?.Invoke(null, EventArgs.Empty);
            }
        }





        /// <summary>
        /// 初始化数据库
        /// </summary>
        public void InitDataBase()
        {
            string connectstr = new SqliteConnectionStringBuilder()
            {
                DataSource = "game.sqlite",
                Mode = SqliteOpenMode.ReadWriteCreate
            }.ToString();

            connection = new SqliteConnection(connectstr);

            connection.Open();
        }

        /// <summary>
        /// 增加数据
        /// </summary>
        /// <param name="stamp"></param>
        /// <param name="total"></param>
        /// <param name="month"></param>
        /// <param name="day"></param>
        public void AddRecord(DateTime stamp, uint total, uint month, uint day)
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                var addcmd = new SqliteCommand();
                addcmd.Connection = connection;
                addcmd.CommandText = "INSERT INTO ProdTable VALUES (@Stamp, @Total, @Month, @Day)";
                addcmd.Parameters.AddWithValue("@Stamp", stamp);
                addcmd.Parameters.AddWithValue("@Total", total);
                addcmd.Parameters.AddWithValue("@Month", month);
                addcmd.Parameters.AddWithValue("@Day", day);
                addcmd.ExecuteReader();
                RecordsChanged?.Invoke(null, EventArgs.Empty);
            }
        }

        public DataTable getDataTable(string startstamp)
        {

            if (connection.State == ConnectionState.Open)
            {
                string filterstr = "SELECT * FROM ProdTable";
                if (!string.IsNullOrEmpty(startstamp))
                {
                    filterstr = string.Format("SELECT * FROM ProdTable WHERE TimeStamp>'{0}'", startstamp);
                }
                SqliteCommand cmd = new SqliteCommand(filterstr, connection);
                SqliteDataReader sdr = cmd.ExecuteReader();

                DataTable data = new DataTable();
                data.Load(sdr, LoadOption.OverwriteChanges);
                FilterTable = data;
                return data;
            }
            else
            {
                FilterTable = null;
                return null;
            }
        }
        public bool ExportDataTableToCSVFile(string path)
        {
            if (FilterTable == null)
                return false;
            using (StreamWriter sw = new StreamWriter(path, false, Encoding.UTF8))
            {
                sw.WriteLine("时间, 总产量, 月产量, 班产,");
                for (int i = 0; i < FilterTable.Rows.Count; i++)
                {
                    string linecon = string.Empty;
                    for (int j = 0; j < FilterTable.Columns.Count; j++)
                        linecon += FilterTable.Rows[i][j].ToString() + ",";
                    sw.WriteLine(linecon);
                }
            }
            return true;
        }
    }
}